/* in order of foreign key constraints */
drop table if exists astro_tags
	, astro_post_statistics
	, astro_userroles
	, astro_users
	, astro_logs
	, astro_comments
	, astro_objectstore
	, astro_versionstore
	, astro_posts
	, astro_categories
	, astro_rolepermissions
	, astro_rolecategorypermissions
;

CREATE TABLE astro_users(
	Id INT NOT NULL AUTO_INCREMENT,
	Name varchar(128) CHARACTER SET utf8  NOT NULL,
	Email varchar(128) CHARACTER SET utf8  NOT NULL,
	ProperName varchar(255) CHARACTER SET utf8  NULL,
	TimeZoneOffSet DOUBLE NOT NULL,
	Bio varchar(2000) CHARACTER SET utf8  NULL,
	Avatar varchar(255) CHARACTER SET utf8  NULL,
	PublicEmail varchar(255) CHARACTER SET utf8  NULL,
	WebSite varchar(255) CHARACTER SET utf8  NULL,
	PropertyKeys text CHARACTER SET utf8  NULL,
	PropertyValues text CHARACTER SET utf8  NULL,
	Password varchar(128) CHARACTER SET utf8  NOT NULL,
	Password_Salt varchar(128) CHARACTER SET utf8  NOT NULL,
	PasswordFormat INT NOT NULL,
	UniqueId char(38) NOT NULL /*CONSTRAINT DF_astro_users_UniqueId DEFAULT newid()*/,
	CONSTRAINT IX_astro_users_Id PRIMARY KEY ( Id ), /* mySQL forces AUTO_INCREMENT columns to be the PK */
	CONSTRAINT PK_astro_users UNIQUE INDEX ( Name ),
	CONSTRAINT IX_astro_users_Email UNIQUE INDEX ( Email ),
	CONSTRAINT IX_astro_users_UniqueId UNIQUE INDEX ( UniqueId )
)
;

/*drop table if exists astro_categories
;*/

CREATE TABLE astro_categories(
	Id INT NOT NULL AUTO_INCREMENT,
	Name varchar(255) CHARACTER SET utf8  NOT NULL,
	View varchar(64) CHARACTER SET utf8  NULL,
	PostView varchar(64) CHARACTER SET utf8  NULL,
	FormattedName varchar(255) CHARACTER SET utf8  NOT NULL,
	LinkName varchar(255) CHARACTER SET utf8  NOT NULL,
	FeedUrlOverride varchar(255) CHARACTER SET utf8  NULL,
	Body text CHARACTER SET utf8  NULL,
	IsDeleted boolean NOT NULL /*CONSTRAINT [DF_astro_categories_IsDeleted] */ DEFAULT 0,
	Post_Count INT NOT NULL /*CONSTRAINT [DF_astro_categories_Post_Count] */ DEFAULT 0,
	UniqueId char(38) NOT NULL /*CONSTRAINT [DF_astro_categories_UniqueId]  DEFAULT (newid())*/,
	ParentId INT NOT NULL /*CONSTRAINT [DF_astro_categories_ParentId] */ DEFAULT -1,
	Type INT NOT NULL /*CONSTRAINT [DF_astro_categories_Type] */ DEFAULT 0,
	ImageUrl varchar(255) CHARACTER SET utf8  NULL,
	MetaDescription varchar(255) CHARACTER SET utf8  NULL,
	MetaKeywords varchar(255) CHARACTER SET utf8  NULL,

	SortOrdering INT NOT NULL DEFAULT 0,
	IsSearching boolean NOT NULL DEFAULT 0,
	IsVersioning boolean NOT NULL DEFAULT 0,
	IsRouting boolean NOT NULL DEFAULT 0,
	PropertyKeys text CHARACTER SET utf8  NULL,
	PropertyValues text CHARACTER SET utf8  NULL,
	FeaturedId INT NOT NULL /*CONSTRAINT [DF_astro_categories_FeaturedId] */ DEFAULT 0,
	SortOrderTypeId INT NOT NULL /*CONSTRAINT [DF_astro_categories_SortTypeTypeId] */ DEFAULT 0,
	ExcludeSubCategoryPosts boolean NOT NULL /*CONSTRAINT [DF_astro_categories_ExcludeSubCategoryPosts] */ DEFAULT 0,
	CONSTRAINT PK_astro_categories PRIMARY KEY ( Id ),
	CONSTRAINT IX_astro_categories_LinkName_ParentId UNIQUE INDEX ( LinkName, ParentId ),
	CONSTRAINT IX_astro_categories_Name_ParentId UNIQUE INDEX ( Name, ParentId )
)
;

/*drop table if exists astro_posts
;*/

CREATE TABLE astro_posts(
	Id INT NOT NULL AUTO_INCREMENT,
	Title varchar(255) CHARACTER SET utf8  NOT NULL,
	PostBody text CHARACTER SET utf8  NOT NULL,
	CreatedOn datetime NOT NULL,
	ModifiedOn datetime NOT NULL,
	Status INT NOT NULL /*CONSTRAINT [DF_astro_posts_Status] */ DEFAULT 1,
	Content_Type varchar(50) CHARACTER SET utf8  NOT NULL,
	Name varchar(255) CHARACTER SET utf8  NOT NULL,
	Comment_Count INT NOT NULL /*CONSTRAINT [DF_astro_posts_Comment_Count] */ DEFAULT 0,
	Tag_List varchar(512) CHARACTER SET utf8  NULL,
	CategoryId INT NOT NULL,
	Version INT NOT NULL /*CONSTRAINT [DF_astro_posts_Version] */ DEFAULT 0,
	ModifiedBy varchar(128) CHARACTER SET utf8  NULL,
	CreatedBy varchar(128) CHARACTER SET utf8  NOT NULL,
	ExtendedBody text CHARACTER SET utf8  NULL,
	IsDeleted boolean NOT NULL /*CONSTRAINT [DF_astro_posts_IsDeleted] */ DEFAULT 0,
	Published datetime NOT NULL,
	Pending_Comment_Count INT NOT NULL /*CONSTRAINT [DF_astro_posts_Pending_Comment_Count] */ DEFAULT 0,
	Views INT NOT NULL /*CONSTRAINT [DF_astro_posts_Views] */ DEFAULT 0,
	UniqueId char(38) NOT NULL /*CONSTRAINT [DF_astro_posts_UniqueId]  DEFAULT (newid())*/,
	EnableComments boolean NOT NULL /*CONSTRAINT [DF_astro_posts_EnableComments] */ DEFAULT 1,
	PropertyKeys text CHARACTER SET utf8 NULL,
	PropertyValues text CHARACTER SET utf8 NULL,
	UserName varchar(128) CHARACTER SET utf8  NOT NULL,
	Notes varchar(2000) CHARACTER SET utf8  NULL,
	ImageUrl varchar(255) CHARACTER SET utf8  NULL,
	MetaDescription varchar(255) CHARACTER SET utf8  NULL,
	MetaKeywords varchar(255) CHARACTER SET utf8  NULL,
	IsPublished boolean NOT NULL /*CONSTRAINT [DF_astro_posts_IsPublished] */ DEFAULT 0,
	SortOrder INT NOT NULL /*CONSTRAINT [DF_astro_posts_SortOrder] */ DEFAULT 0,
	ParentId INT NOT NULL /*CONSTRAINT [DF_astro_posts_ParentId] */ DEFAULT 0,
	IsHome boolean NOT NULL /*CONSTRAINT [DF_astro_posts_IsHome] */ DEFAULT 0,
	HomeSortOrder INT NOT NULL /*CONSTRAINT [DF_astro_posts_HomeSortOrder] */ DEFAULT 0,
	CONSTRAINT PK_astro_posts PRIMARY KEY ( Id ),
	CONSTRAINT IX_astro_posts_CategoryId_Name UNIQUE INDEX ( CategoryId, Name ),
	CONSTRAINT FOREIGN KEY FK_astro_posts_astro_categories ( CategoryId )
		REFERENCES astro_categories ( Id )
)
;

/*drop table if exists astro_versionstore
;*/

CREATE TABLE astro_versionstore(
	Id INT NOT NULL AUTO_INCREMENT,
	UniqueId char(38) NOT NULL /*CONSTRAINT [DF_astro_versionstore_UniqueId]  DEFAULT (newid())*/,
	Data text CHARACTER SET utf8  NOT NULL,
	Type varchar(128) CHARACTER SET utf8  NOT NULL,
	Version INT NOT NULL,
	CreatedBy varchar(128) CHARACTER SET utf8  NOT NULL,
	CreatedOn datetime NOT NULL,
	Name varchar(255) CHARACTER SET utf8  NOT NULL,
	ItemId INT NOT NULL /*CONSTRAINT [DF_astro_versionstore_ItemId] */ DEFAULT 0,
	Notes varchar(2000) CHARACTER SET utf8  NULL,
	CONSTRAINT PK_astro_versionstore PRIMARY KEY ( Id )
)
;

/*drop table if exists astro_objectstore
;*/

CREATE TABLE astro_objectstore(
	Id INT NOT NULL AUTO_INCREMENT,
	Name varchar(128) CHARACTER SET utf8  NOT NULL,
	Data text CHARACTER SET utf8  NOT NULL,
	Type varchar(255) CHARACTER SET utf8  NOT NULL,
	CreatedOn datetime NOT NULL,
	ModifiedOn datetime NOT NULL,
	Content_Type varchar(128) CHARACTER SET utf8  NOT NULL,
	Version INT NOT NULL,
	UniqueId char(38) NOT NULL /*CONSTRAINT [DF_astro_objectstore_UniqueId]  DEFAULT (newid())*/,
	CONSTRAINT PK_ObjectStore PRIMARY KEY ( Id ),
	CONSTRAINT IX_astro_objectstore_Name UNIQUE INDEX ( Name )
)
;

/*drop table if exists astro_comments
;*/

CREATE TABLE astro_comments(
	Id INT NOT NULL AUTO_INCREMENT,
	PostId INT NOT NULL,
	Body varchar(4000) CHARACTER SET utf8  NOT NULL,
	CreatedBy varchar(128) CHARACTER SET utf8  NULL,
	Published datetime NOT NULL,
	Name varchar(128) CHARACTER SET utf8  NOT NULL,
	IsPublished boolean NOT NULL,
	Version INT NOT NULL,
	WebSite varchar(512) CHARACTER SET utf8  NULL,
	SpamScore INT NOT NULL,
	IPAddress varchar(64) CHARACTER SET utf8  NULL,
	ModifiedOn datetime NOT NULL,
	ModifiedBy varchar(128) CHARACTER SET utf8  NULL,
	Email varchar(128) CHARACTER SET utf8  NULL,
	PropertyKeys text CHARACTER SET utf8  NULL,
	PropertyValues text CHARACTER SET utf8  NULL,
	CommentTypeId varchar(50) CHARACTER SET utf8  NULL,
	IsDeleted boolean NOT NULL /*CONSTRAINT [DF_astro_comments_IsDeleted] */ DEFAULT 0,
	UniqueId char(38) NOT NULL /*CONSTRAINT [DF_astro_comments_UniqueId]  DEFAULT (newid())*/,
	UserName varchar(128) CHARACTER SET utf8  NULL,
	IsTrackback boolean NOT NULL /*CONSTRAINT [DF_astro_comments_IsTrackback] */ DEFAULT 0,
	CONSTRAINT PK_astro_comments PRIMARY KEY ( Id ),
	INDEX IX_astro_comments_PostId ( PostId ),
	CONSTRAINT FK_astro_comments_astro_posts FOREIGN KEY ( PostId )
		REFERENCES astro_posts ( Id )
)
;

/*drop table if exists astro_logs
;*/

CREATE TABLE astro_logs(
	Id INT NOT NULL AUTO_INCREMENT,
	Type INT NOT NULL,
	Title varchar(255) CHARACTER SET utf8  NOT NULL,
	Message varchar(2000) CHARACTER SET utf8  NOT NULL,
	CreatedBy varchar(128) CHARACTER SET utf8  NOT NULL,
	CreatedOn datetime NOT NULL,
	CONSTRAINT PK_astro_logs PRIMARY KEY ( Id )
)
;

/*drop table if exists astro_userroles
;*/

CREATE TABLE astro_userroles(
	Id INT NOT NULL AUTO_INCREMENT,
	UserId INT NOT NULL,
	RoleName varchar(128) CHARACTER SET utf8  NOT NULL,
	CONSTRAINT PK_astro_userroles PRIMARY KEY ( Id ),
	CONSTRAINT IX_astro_userroles_RoleName_UserId UNIQUE INDEX ( RoleName, UserId ),
	CONSTRAINT FK_astro_userroles_astro_users FOREIGN KEY ( UserId )
		REFERENCES astro_users ( Id )
)
;

/*drop table if exists astro_post_statistics
;*/

CREATE TABLE astro_post_statistics(
	Id INT NOT NULL AUTO_INCREMENT,
	PostId INT NOT NULL,
	DateViewed datetime NOT NULL,
	CONSTRAINT PK_astro_post_statistics PRIMARY KEY ( Id ),
	CONSTRAINT FK_astro_post_statistics_astro_posts FOREIGN KEY ( PostId )
		REFERENCES astro_posts ( Id )
)
;

/*drop table if exists astro_tags
;*/

CREATE TABLE astro_tags(
	Id INT NOT NULL AUTO_INCREMENT,
	Name varchar(128) CHARACTER SET utf8  NOT NULL,
	PostId INT NOT NULL,
	CONSTRAINT PK_astro_tags PRIMARY KEY ( Id ),
	INDEX IX_astro_tags_PostId ( PostId ),
	CONSTRAINT IX_astro_tags_Name_PostId UNIQUE INDEX ( Name, PostId ),
	CONSTRAINT FK_astro_tags_astro_posts FOREIGN KEY ( PostId )
		REFERENCES astro_posts ( Id )
)
;

/*drop table if exists astro_rolepermissions
;*/

CREATE TABLE astro_rolepermissions(
	Id INT NOT NULL AUTO_INCREMENT,
	RoleName varchar(128) CHARACTER SET utf8  NOT NULL,
	HasRead boolean NOT NULL /*CONSTRAINT [DF_astro_rolepermissions_HasRead] */ DEFAULT 0,
	HasEdit boolean NOT NULL /*CONSTRAINT [DF_astro_rolepermissions_HasEdit] */ DEFAULT 0,
	HasPublish boolean NOT NULL /*CONSTRAINT [DF_astro_rolepermissions_HasPublish] */ DEFAULT 0,
	CreatedBy varchar(128) CHARACTER SET utf8  NULL,
	ModifiedOn datetime NOT NULL,
	ModifiedBy varchar(128) CHARACTER SET utf8  NULL,
	CONSTRAINT PK_astro_rolepermissions PRIMARY KEY ( Id ),
	INDEX IX_astro_rolepermissions_RoleName ( RoleName )
)
;

/*drop table if exists astro_rolecategorypermissions
;*/

CREATE TABLE astro_rolecategorypermissions(
	Id INT NOT NULL AUTO_INCREMENT,
	RoleName varchar(128) CHARACTER SET utf8  NOT NULL,
	CategoryId INT NOT NULL,
	HasRead boolean NOT NULL /*CONSTRAINT [DF_astro_rolecategorypermissions_HasRead] */ DEFAULT 0,
	HasEdit boolean NOT NULL /*CONSTRAINT [DF_astro_rolecategorypermissions_HasEdit] */ DEFAULT 0,
	HasPublish boolean NOT NULL /*CONSTRAINT [DF_astro_rolecategorypermissions_HasPublish] */ DEFAULT 0,
	CreatedBy varchar(128) CHARACTER SET utf8  NULL,
	ModifiedOn datetime NOT NULL,
	ModifiedBy varchar(128) CHARACTER SET utf8  NULL,
	CONSTRAINT PK_astro_rolecategorypermissions PRIMARY KEY ( Id ),
	INDEX IX_astro_rolecategorypermissions_RoleName_CategoryId ( RoleName, CategoryId )
)
;

drop view if exists astro_tagweights
;

create view astro_tagweights
AS
select
	t.Name, count(*) AS `WEIGHT`
from
	astro_tags t
group by
	t.Name
;
